using System;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;
using System.Collections.Generic;


using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for ICPCARTable
    /// </summary>
    public class ICPCARTable : CacheAbstraction
    {
		String sxFilterHierarki_Sales =
            @"
            where v.SALES_EMPLOYEE_NO IN
            (
                select :prmID from dual
                UNION ALL
                select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                (
                    select 
                        p_slx_usergroup_id 
                    from 
                        slogix.p_slx_usergroup
                    start with 
                        p_slx_usergroup_id =
                        (
                            select max(a)
                            from 
                            (
                                select 
                                    decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                from 
                                    slogix.p_slx_usergroup_member 
                                where EMPLOYEE_NO = :prmID
                            )
                        ) 
                    connect by prior 
                        p_slx_usergroup_id = parent_id
                )
            )";

        String sxFilterHierarki_CAR =
            @"
            where v.CAR_EMPLOYEE_NO IN
            (  
                select :prmID from dual
                UNION ALL          
                select 
                    NIK 
                from 
                    v_icp_escalation
                start with 
                    NIK = :prmID
                connect by prior 
                    t_qrtz_user_destination_id = parent_id
            )";
			
        public DataTable GetCARList()
        {
            return GetCARList(false);
        }
        public DataTable GetCARList(bool bForceRefresh)
        {
            String sxCacheKey = KCache.ICP_CAR+"_"+ICPUser.ID;

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                String sxQuery = String.Empty;
                DataTable dt;
                if (ICPUser.IsAdmin)
                {
                    sxQuery =
                        @"
                    select distinct
                        EMPLOYEE_NO,
                        EMPLOYEE_NAME
                    from 
                        V_ICP_CAR
                    order by
                        EMPLOYEE_NAME ASC
                    ";

                    
                    dt = dbl.GetDataTable(sxQuery);
                }
                else
                {
                    List<OracleParameter> lsParams = new List<OracleParameter>();
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
//                    sxQuery =
//                        @"
//                        select distinct
//                            v.EMPLOYEE_NO,
//                            v.EMPLOYEE_NAME
//                        from 
//                            V_ICP_CAR v {0}
//                        order by
//                            v.EMPLOYEE_NAME ASC";
                    /*sxQuery = @"select distinct EMPLOYEE_NO,
                        EMPLOYEE_NAME
    from sni.v_hr_employee a
    where a.p_organization_id_sub = (
            select p_organization_id_sub
            from sni.v_hr_employee b
    ) and a.job_position_code not like 'Assistant Manager'";*/
                    sxQuery =
                        @"
                        select distinct
                            v.CAR_EMPLOYEE_NO EMPLOYEE_NO,
                            v.CAR_NAME EMPLOYEE_NAME 
                        from 
                            V_ICP_COMPANY_CAR_SALES v {0}
                        order by
                            v.CAR_NAME asc";

                    if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum || ICPUser.RoleId == (int)ICPUserRole.CARPimpinanCAR)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
                    }
                    else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
                    }
                    else
                    {
                        sxQuery = String.Format(sxQuery, String.Empty);
                    }
//                    sxQuery =
//                        @"
//                    select distinct
//                        EMPLOYEE_NO,
//                        EMPLOYEE_NAME
//                    from 
//                        V_ICP_CAR
//                    where
//                        EMPLOYEE_NO IN
//                        (
//                            SELECT DISTINCT NIK
//                            FROM 
//                            v_icp_escalation
//                            START WITH NIK = :prmID        
//                            CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
//                        )
//                    order by
//                        EMPLOYEE_NAME ASC
//                    ";

                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable GetCARListByCompany(int iCustomerId)
        {
            return GetCARListByCompany(iCustomerId, false);
        }
        public DataTable GetCARListByCompany(int iCustomerId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.ICP_CAR+iCustomerId.ToString();

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmCustomerID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCustomerId;
                String sxQuery =
                    @"
                    select distinct
                        EMPLOYEE_NO,
                        EMPLOYEE_NAME
                    from 
                        V_ICP_CAR
                    where
                        COMPANY_ID = :prmCustomerID
                    order by
                        EMPLOYEE_NAME ASC
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public String GetCARNameByID(int iCARId)
        {
            return GetCARNameByID(iCARId, false);
        }
        public String GetCARNameByID(int iCARId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.ICP_CAR + iCARId.ToString();

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmCARID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCARId;
                String sxQuery =
                    @"
                    select distinct
                        EMPLOYEE_NAME
                    from 
                        V_ICP_CAR
                    where
                        EMPLOYEE_NO = :prmCARID
                    order by
                        EMPLOYEE_NAME ASC
                    ";

                String sxReturn = dbl.GetSingleValue(sxQuery, lsParams.ToArray()).ToString();

                AddToCache(sxCacheKey, sxReturn, CacheType.Daily);

            }
            return (String)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.CAR_LIST;
        }
    }
}